<!DOCTYPE html><html><head><title>MySQL学习笔记（Day004：权限拾遗/Role模拟/Workbench/体系结构）</title><meta charset='utf-8'><link href='https://dn-maxiang.qbox.me/res-min/themes/marxico.css' rel='stylesheet'></head><body><div id='preview-contents' class='note-content'>
                        <div id="wmd-preview" class="preview-content"></div>
                    <div id="wmd-preview-section-1" class="wmd-preview-section preview-content">

</div><div id="wmd-preview-section-2" class="wmd-preview-section preview-content">

<h1 id="mysql学习笔记day004权限拾遗role模拟workbench体系结构">MySQL学习笔记（Day004：权限拾遗/Role模拟/Workbench/体系结构）</h1>

<p><p class="note-tags "><code class="notebook">MySQL学习</code> </p></p>

<div><div class="toc"><div class="toc">
<ul>
<li><a href="#mysql学习笔记day004权限拾遗role模拟workbench体系结构">MySQL学习笔记（Day004：权限拾遗/Role模拟/Workbench/体系结构）</a><ul>
<li><a href="#一-权限拾遗">一. 权限拾遗</a><ul>
<li><a href="#1-grant与创建用户">1. GRANT与创建用户</a></li>
<li><a href="#2-查看某一个用户的权限">2. 查看某一个用户的权限</a></li>
<li><a href="#3-删除某一个用户">3. 删除某一个用户</a></li>
<li><a href="#4-mysql权限信息">4. MySQL权限信息</a></li>
<li><a href="#5-informationschema">5. information_schema</a></li>
</ul>
</li>
<li><a href="#二-mysql模拟角色">二. MySQL模拟角色</a><ul>
<li><a href="#1-角色的定义">1. 角色的定义：</a></li>
<li><a href="#2-模拟角色操作">2. 模拟角色操作：</a></li>
</ul>
</li>
<li><a href="#三-workbench与utilities介绍">三. Workbench与Utilities介绍</a><ul>
<li><a href="#1-下载">1. 下载</a></li>
<li><a href="#2-workbench功能概述">2. Workbench功能概述</a></li>
<li><a href="#3-utilities安装">3. Utilities安装</a></li>
</ul>
</li>
<li><a href="#四-mysql体系结构">四. MySQL体系结构</a><ul>
<li><a href="#1-数据库">1. 数据库</a></li>
<li><a href="#2-数据库实例">2. 数据库实例</a></li>
<li><a href="#3-mysql体系结构">3. MySQL体系结构</a></li>
<li><a href="#4-mysql物理存储结构">4. MySQL物理存储结构</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
</div>

</div><div id="wmd-preview-section-3" class="wmd-preview-section preview-content">

<h2 id="一-权限拾遗">一. 权限拾遗</h2>

</div><div id="wmd-preview-section-4" class="wmd-preview-section preview-content">

<h3 id="1-grant与创建用户">1. GRANT与创建用户</h3>

</div><div id="wmd-preview-section-5" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">grant</span> <span class="hljs-keyword">select</span> <span class="hljs-keyword">on</span> sys.* <span class="hljs-keyword">to</span> <span class="hljs-string">'perf'</span>@<span class="hljs-string">'127.0.0.1'</span> <span class="hljs-keyword">identified</span> <span class="hljs-keyword">by</span> <span class="hljs-string">'123'</span>;</span><br>Query OK, 0 rows affected, 1 warning (0.01 sec) <span class="hljs-comment">-- 这里有一个warning</span><br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">warnings</span>;</span><br><span class="hljs-comment">-- 输入warning的Message如下：</span><br><span class="hljs-comment">-- Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement.</span><br></code></pre>

<p><strong>上面的这个例子使用<code>GRANT</code>赋权限的同时创建了<code>'perf'@'127.0.0.1'</code>这个用户，但是出现了<code>warning</code>，从给出的提示看来，以后的MySQL版本会废弃掉这种方式</strong></p>

<ul><li><strong>正确的创建用户并赋权的方式</strong></li>
</ul>

</div><div id="wmd-preview-section-6" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">user</span> <span class="hljs-string">'pref'</span>@<span class="hljs-string">'127.0.0.1'</span> <span class="hljs-keyword">identified</span> <span class="hljs-keyword">by</span> <span class="hljs-string">'123'</span>;</span><br>Query OK, 0 rows affected (0.00sec)<br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">grant</span> <span class="hljs-keyword">select</span> <span class="hljs-keyword">on</span> sys.* <span class="hljs-keyword">to</span> <span class="hljs-string">'perf'</span>@<span class="hljs-string">'127.0.0.1'</span>;</span><br>Query OK, 0 rows affected (0.00sec)<br></code></pre>

</div><div id="wmd-preview-section-7" class="wmd-preview-section preview-content">

<h3 id="2-查看某一个用户的权限">2. 查看某一个用户的权限</h3>

</div><div id="wmd-preview-section-8" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">grants</span> <span class="hljs-keyword">for</span> <span class="hljs-string">'perf'</span>@<span class="hljs-string">'127.0.0.1'</span>;</span>   <br>+<span class="hljs-comment">-----------------------------------------------+</span><br>| Grants for perf@127.0.0.1                     |<br>+<span class="hljs-comment">-----------------------------------------------+</span><br>| <span class="hljs-operator"><span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">USAGE</span> <span class="hljs-keyword">ON</span> *.* <span class="hljs-keyword">TO</span> <span class="hljs-string">'perf'</span>@<span class="hljs-string">'127.0.0.1'</span>      | <span class="hljs-comment">-- USAGE表示用户可以登录</span><br>| <span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">ON</span> <span class="hljs-string">`sys`</span>.* <span class="hljs-keyword">TO</span> <span class="hljs-string">'perf'</span>@<span class="hljs-string">'127.0.0.1'</span> | <span class="hljs-comment">-- 对sys库的所有表有select权限</span><br>+<span class="hljs-comment">-----------------------------------------------+</span><br><span class="hljs-number">2</span> <span class="hljs-keyword">rows</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

</div><div id="wmd-preview-section-9" class="wmd-preview-section preview-content">

<h3 id="3-删除某一个用户">3. 删除某一个用户</h3>

</div><div id="wmd-preview-section-10" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">drop</span> <span class="hljs-keyword">user</span> <span class="hljs-string">'perf'</span>@<span class="hljs-string">'127.0.0.1'</span>;</span><br>Query OK, 0 rows affected (0.00sec)<br></code></pre>

</div><div id="wmd-preview-section-11" class="wmd-preview-section preview-content">

<h3 id="4-mysql权限信息">4. MySQL权限信息</h3>

</div><div id="wmd-preview-section-12" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> mysql.<span class="hljs-keyword">user</span> <span class="hljs-keyword">where</span> <span class="hljs-keyword">user</span>=<span class="hljs-string">'perf'</span>\G<br>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br>                  Host: <span class="hljs-number">127.0</span><span class="hljs-number">.0</span><span class="hljs-number">.1</span><br>                  <span class="hljs-keyword">User</span>: perf<br>           Select_priv: N   <span class="hljs-comment">---由于perf用户是对sys库有权限，所以这里（USER）全是N</span><br>           Insert_priv: N<br>           Update_priv: N<br>           Delete_priv: N<br>           Create_priv: N<br>             Drop_priv: N<br>           Reload_priv: N<br>         Shutdown_priv: N<br>          Process_priv: N<br>             File_priv: N<br>            Grant_priv: N<br>       References_priv: N<br>            Index_priv: N<br>            Alter_priv: N<br>          Show_db_priv: N<br>            Super_priv: N<br> Create_tmp_table_priv: N<br>      Lock_tables_priv: N<br>          Execute_priv: N<br>       Repl_slave_priv: N<br>      Repl_client_priv: N<br>      Create_view_priv: N<br>        Show_view_priv: N<br>   Create_routine_priv: N<br>    Alter_routine_priv: N<br>      Create_user_priv: N<br>            Event_priv: N<br>          Trigger_priv: N<br>Create_tablespace_priv: N<br>              ssl_type: <br>            ssl_cipher: <br>           x509_issuer: <br>          x509_subject: <br>         max_questions: <span class="hljs-number">0</span><br>           max_updates: <span class="hljs-number">0</span><br>       max_connections: <span class="hljs-number">0</span><br>  max_user_connections: <span class="hljs-number">0</span><br>                <span class="hljs-keyword">plugin</span>: mysql_native_password<br> authentication_string: *<span class="hljs-number">23</span>AE809DDACAF96AF0FD78ED04B6A265E05AA257<br>      password_expired: N<br> password_last_changed: <span class="hljs-number">2015</span>-<span class="hljs-number">11</span>-<span class="hljs-number">18</span> <span class="hljs-number">12</span>:<span class="hljs-number">20</span>:<span class="hljs-number">13</span><br>     password_lifetime: <span class="hljs-literal">NULL</span><br>        account_locked: N    <span class="hljs-comment">-- 如果这里为Y，该用户就无法使用了</span><br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">------------------------我是分割线-----------------------------</span><br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> mysql.db <span class="hljs-keyword">where</span> <span class="hljs-keyword">user</span>=<span class="hljs-string">'perf'</span>\G    <br>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br>                 Host: <span class="hljs-number">127.0</span><span class="hljs-number">.0</span><span class="hljs-number">.1</span><br>                   Db: sys  <span class="hljs-comment">-- sys 数据库</span><br>                 <span class="hljs-keyword">User</span>: perf<br>          Select_priv: Y    <span class="hljs-comment">-- 有select权限，和我们赋予perf的权限一致</span><br>          Insert_priv: N<br>          Update_priv: N<br>          Delete_priv: N<br>          Create_priv: N<br>            Drop_priv: N<br>           Grant_priv: N<br>      References_priv: N<br>           Index_priv: N<br>           Alter_priv: N<br>Create_tmp_table_priv: N<br>     Lock_tables_priv: N<br>     Create_view_priv: N<br>       Show_view_priv: N<br>  Create_routine_priv: N<br>   Alter_routine_priv: N<br>         Execute_priv: N<br>           Event_priv: N<br>         Trigger_priv: N<br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<blockquote>
  <p><strong>注意：</strong> <br>
  <strong><code>不建议</code>使用<code>INSERT</code>或者<code>GRANT</code>对元数据表进行修改，来达到修改权限的目的</strong></p>
</blockquote>

</div><div id="wmd-preview-section-13" class="wmd-preview-section preview-content">

<h3 id="5-informationschema">5. information_schema</h3>

</div><div id="wmd-preview-section-14" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-keyword">user</span>();</span><br>+<span class="hljs-comment">----------------+</span><br>| user()         |<br>+<span class="hljs-comment">----------------+</span><br>| perf@127.0.0.1 |<br>+<span class="hljs-comment">----------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">databases</span>;</span><br>+<span class="hljs-comment">--------------------+</span><br>| Database           |<br>+<span class="hljs-comment">--------------------+</span><br>| information_schema |  <span class="hljs-comment">-- 这是一个特殊的数据库，我们虽然可以看见，但其实没有权限</span><br>| sys                |<br>+<span class="hljs-comment">--------------------+</span><br>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">use</span> information_schema;</span><br>Reading table information for completion of table and column names<br>You can turn off this feature to get a quicker startup with -A<br><br>Database changed<br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span>  INNODB_CMP;</span><br>ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation<br></code></pre>

<hr>

</div><div id="wmd-preview-section-15" class="wmd-preview-section preview-content">

<h2 id="二-mysql模拟角色">二. MySQL模拟角色</h2>

</div><div id="wmd-preview-section-16" class="wmd-preview-section preview-content">

<h3 id="1-角色的定义">1. 角色的定义：</h3>

<blockquote>
  <p><code>角色</code>(Role)可以用来批量管理用户，同一个角色下的用户，拥有相同的权限。 <br>
  <code>MySQL5.7.X</code>以后可以模拟角色(Role)的功能，通过<code>mysql.proxies_priv</code>模拟实现。 <br>
  <code>mysql.proxies_priv</code>在<code>5.5.X</code>和<code>5.6.X</code>的时候就存在，但是<code>无法模拟</code>角色(Role)功能。</p>
</blockquote>

</div><div id="wmd-preview-section-17" class="wmd-preview-section preview-content">

<h3 id="2-模拟角色操作">2. 模拟角色操作：</h3>

</div><div id="wmd-preview-section-7559" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">-- 感谢 @M062 郭释 同学 对这里提出的修正</span><br><span class="hljs-comment">-- 查看当前proxy是否代开</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"%proxy%"</span>;</span><br>+<span class="hljs-comment">-----------------------------------+-------+</span><br>| Variable_name                     | Value |<br>+<span class="hljs-comment">-----------------------------------+-------+</span><br>| check_proxy_users                 | OFF   |<br>| mysql_native_password_proxy_users | OFF   |<br>| proxy_user                        |       |<br>| sha256_password_proxy_users       | OFF   |<br>+<span class="hljs-comment">-----------------------------------+-------+</span><br>4 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">set</span> <span class="hljs-keyword">global</span> check_proxy_users=<span class="hljs-keyword">ON</span>;</span><br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">global</span> mysql_native_password_proxy_users=<span class="hljs-keyword">ON</span>;</span><br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; exit; <span class="hljs-comment">-- 以上设置参数，对当前会话无效，需要退出后重新登录</span><br>             <span class="hljs-comment">-- 如果有需要，可以设置到my.cnf中去</span><br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"%proxy%"</span>;</span><br>+<span class="hljs-comment">-----------------------------------+-------+</span><br>| Variable_name                     | Value |<br>+<span class="hljs-comment">-----------------------------------+-------+</span><br>| check_proxy_users                 | ON    |<br>| mysql_native_password_proxy_users | ON    |<br>| proxy_user                        |       |<br>| sha256_password_proxy_users       | OFF   |<br>+<span class="hljs-comment">-----------------------------------+-------+</span><br>4 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">-- ---------------至此，下面的权限映射才有意义---------------</span><br><br>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">user</span> <span class="hljs-string">'junior_dba'</span>@<span class="hljs-string">'127.0.0.1'</span>;</span>  <span class="hljs-comment">-- 相当于定于一个 角色(Role),省略密码，仅为演示</span><br>                                              <span class="hljs-comment">-- 但这只是个普通的用户，名字比较有(Role)的感觉</span><br>                                              <span class="hljs-comment">-- 有点类似用户组</span><br>Query OK, 0 rows affected (0.00sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">user</span> <span class="hljs-string">'tom'</span>@<span class="hljs-string">'127.0.0.1'</span>;</span>         <span class="hljs-comment">-- 用户1，省略密码，仅为演示</span><br>Query OK, 0 rows affected (0.02sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">user</span> <span class="hljs-string">'jim'</span>@<span class="hljs-string">'127.0.0.1'</span>;</span>         <span class="hljs-comment">-- 用户2，省略密码，仅为演示</span><br>Query OK, 0 rows affected (0.02sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">grant</span> proxy <span class="hljs-keyword">on</span> <span class="hljs-string">'junior_dba'</span>@<span class="hljs-string">'127.0.0.1'</span> <span class="hljs-keyword">to</span> <span class="hljs-string">'tom'</span>@<span class="hljs-string">'127.0.0.1'</span>;</span>  <span class="hljs-comment">-- 将junior_dba的权限映射(map)到tom</span><br>Query OK, 0 rows affected (0.02sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">grant</span> proxy <span class="hljs-keyword">on</span> <span class="hljs-string">'junior_dba'</span>@<span class="hljs-string">'127.0.0.1'</span> <span class="hljs-keyword">to</span> <span class="hljs-string">'jim'</span>@<span class="hljs-string">'127.0.0.1'</span>;</span>  <span class="hljs-comment">-- 然后映射(map)给jim</span><br>Query OK, 0 rows affected (0.01sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">grant</span> <span class="hljs-keyword">select</span> <span class="hljs-keyword">on</span> *.* <span class="hljs-keyword">to</span> <span class="hljs-string">'junior_dba'</span>@<span class="hljs-string">'127.0.0.1'</span>;</span>  <span class="hljs-comment">-- 给junior_dba（模拟的Role）赋予实际权限</span><br>Query OK, 0 rows affected (0.01 sec)<br><br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">grants</span> <span class="hljs-keyword">for</span> <span class="hljs-string">'junior_dba'</span>@<span class="hljs-string">'127.0.0.1'</span>;</span>        <span class="hljs-comment">-- 查看 junior_dba的权限</span><br>+<span class="hljs-comment">-------------------------------------------------+</span><br>| Grants for junior_dba@127.0.0.1                 |<br>+<span class="hljs-comment">-------------------------------------------------+</span><br>| <span class="hljs-operator"><span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">ON</span> *.* <span class="hljs-keyword">TO</span> <span class="hljs-string">'junior_dba'</span>@<span class="hljs-string">'127.0.0.1'</span> |<br>+<span class="hljs-comment">-------------------------------------------------+</span><br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">grants</span> <span class="hljs-keyword">for</span> <span class="hljs-string">'jim'</span>@<span class="hljs-string">'127.0.0.1'</span>;</span>               <span class="hljs-comment">-- 查看jim的权限</span><br>+<span class="hljs-comment">--------------------------------------------------------------+</span><br>| Grants for jim@127.0.0.1                                     |<br>+<span class="hljs-comment">--------------------------------------------------------------+</span><br>| <span class="hljs-operator"><span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">USAGE</span> <span class="hljs-keyword">ON</span> *.* <span class="hljs-keyword">TO</span> <span class="hljs-string">'jim'</span>@<span class="hljs-string">'127.0.0.1'</span>                      |<br>| <span class="hljs-keyword">GRANT</span> PROXY <span class="hljs-keyword">ON</span> <span class="hljs-string">'junior_dba'</span>@<span class="hljs-string">'127.0.0.1'</span> <span class="hljs-keyword">TO</span> <span class="hljs-string">'jim'</span>@<span class="hljs-string">'127.0.0.1'</span> |<br>+<span class="hljs-comment">--------------------------------------------------------------+</span><br><span class="hljs-number">2</span> <span class="hljs-keyword">rows</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">grants</span> <span class="hljs-keyword">for</span> <span class="hljs-string">'tom'</span>@<span class="hljs-string">'127.0.0.1'</span>;</span>               <span class="hljs-comment">-- 查看tom的权限 </span><br>+<span class="hljs-comment">--------------------------------------------------------------+</span><br>| Grants for tom@127.0.0.1                                     |<br>+<span class="hljs-comment">--------------------------------------------------------------+</span><br>| <span class="hljs-operator"><span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">USAGE</span> <span class="hljs-keyword">ON</span> *.* <span class="hljs-keyword">TO</span> <span class="hljs-string">'tom'</span>@<span class="hljs-string">'127.0.0.1'</span>                      |<br>| <span class="hljs-keyword">GRANT</span> PROXY <span class="hljs-keyword">ON</span> <span class="hljs-string">'junior_dba'</span>@<span class="hljs-string">'127.0.0.1'</span> <span class="hljs-keyword">TO</span> <span class="hljs-string">'tom'</span>@<span class="hljs-string">'127.0.0.1'</span> |<br>+<span class="hljs-comment">--------------------------------------------------------------+</span><br><span class="hljs-number">2</span> <span class="hljs-keyword">rows</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> mysql.proxies_priv;</span>    <span class="hljs-comment">--  查看 proxies_priv的权限</span><br>+<span class="hljs-comment">-----------+------+--------------+--------------+------------+----------------------+---------------------+</span><br>| Host      | User | Proxied_host | Proxied_user | With_grant | Grantor              | Timestamp           |<br>+<span class="hljs-comment">-----------+------+--------------+--------------+------------+----------------------+---------------------+</span><br>| localhost | root |              |              |          1 | boot@connecting host | 0000-00-00 00:00:00 |<br>| 127.0.0.1 | tom  | 127.0.0.1    | junior_dba   |          0 | root@localhost       | 0000-00-00 00:00:00 |<br>| 127.0.0.1 | jim  | 127.0.0.1    | junior_dba   |          0 | root@localhost       | 0000-00-00 00:00:00 |<br>+<span class="hljs-comment">-----------+------+--------------+--------------+------------+----------------------+---------------------+</span><br>3 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">-- 测试jim的权限</span><br><span class="hljs-comment">-- 如果不按照@M062 郭释同学给出的修正，</span><br><span class="hljs-comment">-- 即便映射了junior_dba的select权限，但是jim仍然无法select</span><br><span class="hljs-comment">-- 甚至show databases; 都无法得到所有数据库信息。</span><br>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">user</span>();</span><br>+<span class="hljs-comment">---------------+</span><br>| user()        |<br>+<span class="hljs-comment">---------------+</span><br>| jim@127.0.0.1 |<br>+<span class="hljs-comment">---------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">databases</span>;</span><br>+<span class="hljs-comment">--------------------+</span><br>| Database           |<br>+<span class="hljs-comment">--------------------+</span><br>| information_schema |<br>| mysql              |<br>| performance_schema |<br>| sys                |<br>+<span class="hljs-comment">--------------------+</span><br>4 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> host,<span class="hljs-keyword">user</span> <span class="hljs-keyword">from</span> mysql.<span class="hljs-keyword">user</span>;</span><br>+<span class="hljs-comment">-----------+------------+</span><br>| host      | user       |<br>+<span class="hljs-comment">-----------+------------+</span><br>| 127.0.0.1 | jim        |<br>| 127.0.0.1 | junior_dba |<br>| 127.0.0.1 | perf       |<br>| 127.0.0.1 | tom        |<br>| localhost | mysql.sys  |<br>| localhost | root       |<br>+<span class="hljs-comment">-----------+------------+</span><br>6 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">database</span> aaa;</span><br>ERROR 1044 (42000): Access denied for user 'junior_dba'@'127.0.0.1' to database 'aaa'<br></code></pre>

<blockquote>
  <p><strong><code>mysql.proxies_priv</code>仅仅是对Role的<code>模拟</code>，和Oracle的角色还是有所不同.官方称呼为<code>Role like</code></strong></p>
  
  <p><code>MySQL5.6.X</code> 模拟Role功能需要安装插件，具体方法请参考<a href="http://www.innomysql.net/AskInside/topic/mysql5-6-%E9%85%8D%E7%BD%AEproxy%E7%94%A8%E6%88%B7-%EF%BC%88%E6%B3%A8%E6%84%8F%E7%94%A8mysql5-6%EF%BC%89" target="_blank">论坛帖子</a> <a href="http://dev.mysql.com/doc/refman/5.6/en/proxy-users.html" target="_blank">官方文档1</a> <a href="http://dev.mysql.com/doc/refman/5.6/en/pluggable-authentication.html" target="_blank">官方文档2</a></p>
</blockquote>

<hr></div><div id="wmd-preview-section-6922" class="wmd-preview-section preview-content">

<h2 id="三-workbench与utilities介绍">三. Workbench与Utilities介绍</h2>

</div><div id="wmd-preview-section-6923" class="wmd-preview-section preview-content">

<h3 id="1-下载">1. 下载</h3>

<ul><li><a href="http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-workbench-community-6.3.5-win32.msi" target="_blank">Workbench-win32下载</a></li>
<li><a href="http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-workbench-community-6.3.5-winx64.msi" target="_blank">Workbench-win64下载</a></li>
<li><a href="http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.5.6.tar.gz" target="_blank">Utilities下载</a></li>
</ul>

</div><div id="wmd-preview-section-6924" class="wmd-preview-section preview-content">

<h3 id="2-workbench功能概述">2. Workbench功能概述</h3>

<ul><li><strong>SQL语句格式化</strong></li>
<li><strong>SQL关键字upcase</strong></li>
<li><strong>MySQL Dashboard</strong></li>
<li><strong>SQL语法提示</strong></li>
<li><strong>ER图</strong></li>
<li><strong>Forward Engine</strong>  <code>//ER图 --&gt; DB表结构</code></li>
<li><strong>Reverse</strong>  <code>//DB表结构 --&gt; ER图</code></li>
</ul>

</div><div id="wmd-preview-section-6925" class="wmd-preview-section preview-content">

<h3 id="3-utilities安装">3. Utilities安装</h3>

</div><div id="wmd-preview-section-6926" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">shell&gt; python setup.py install  <span class="hljs-comment"># 如果安装不成功，查看一下python的版本。推荐2.7.X</span><br></code></pre>

<hr>

</div><div id="wmd-preview-section-6927" class="wmd-preview-section preview-content">

<h2 id="四-mysql体系结构">四. MySQL体系结构</h2>

</div><div id="wmd-preview-section-6928" class="wmd-preview-section preview-content">

<h3 id="1-数据库">1. 数据库</h3>

<blockquote>
  <p>数据库（数据库文件）是一个或者一组二进制文件，通常来说存在与文件系统之上。</p>
</blockquote>

</div><div id="wmd-preview-section-6929" class="wmd-preview-section preview-content">

<h3 id="2-数据库实例">2. 数据库实例</h3>

<blockquote>
  <p>由数据库后台进程/线程以及一个共享区域组成(程序的概念),数据库实例是用来操作数据库文件的</p>
</blockquote>

<p><strong><code>注意：MySQL中，数据库实例和数据库是一一对应的。没有Oracle的一对多(RAC)的机制。</code></strong></p>

</div><div id="wmd-preview-section-6930" class="wmd-preview-section preview-content">

<h3 id="3-mysql体系结构">3. MySQL体系结构</h3>

<ol><li rel="1"><p>单进程多线程结构</p>

<ul>
<li>不会影响MySQL的性能，看程序如何写。（多进程程序，进程间通信开销大于多线程）</li></ul></li>
<li rel="2"><p>存储引擎的概念</p>

<ul>
<li>可以理解成文件系统，例如FAT32, NTFS, EXT4。 一个表是一个分区，引擎就是分区的文件系统</li>
<li><code>存储引擎的对象就是表</code></li>
<li>show tables; 可以看到每个表对应的是上面引擎(Engine)</li>
<li>除了特殊情况，我们现在就只考虑<code>INNODB</code></li></ul></li>
<li rel="3"><p>体系结构图 <br>
<img longdesc="./MySQL体系结构.jpg" alt="MySQL体系结构提" title="" type="image/jpeg" src="" class=""></p></li>
<li rel="4"><p>逻辑存储结构</p>

<table>
<thead>
<tr>
  <th align="center">MySQL逻辑存储结构</th>
</tr>
</thead>
<tbody><tr>
  <td align="center">instance</td>
</tr>
<tr>
  <td align="center">database</td>
</tr>
<tr>
  <td align="center">schema</td>
</tr>
<tr>
  <td align="center">table</td>
</tr>
<tr>
  <td align="center">view</td>
</tr>
</tbody></table>


<ul>
<li><strong>一个DB对应一个schema</strong></li>
<li><strong>一个DB对应一个文件夹</strong></li>
<li><strong>一个表对应一组文件</strong></li></ul></li>
</ol>

</div><div id="wmd-preview-section-6931" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="hljs brainfuck">                                                 <span class="hljs-comment">|</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span>&gt; <span class="hljs-comment">table1</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span> <span class="hljs-comment">|</span> <span class="hljs-comment">view1</span> <span class="hljs-comment">|</span><br><span class="hljs-comment">MySQL</span> <span class="hljs-comment">Instance</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span>&gt; <span class="hljs-comment">Database</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span>&gt; <span class="hljs-comment">Schema</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span>&gt; <span class="hljs-comment">|</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span>&gt; <span class="hljs-comment">table2</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span> <span class="hljs-comment">|</span> <span class="hljs-comment">view2</span> <span class="hljs-comment">|</span><br>                                                 <span class="hljs-comment">|</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span>&gt; <span class="hljs-comment">table3</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span> <span class="hljs-comment">|</span> <span class="hljs-comment">View3</span> <span class="hljs-comment">|</span><br></code></pre>

<blockquote>
  <p><strong>注意：</strong> <br>
  MySQL中一个<code>Database</code>对应一个<code>Schema</code>,之所以要有这个<code>schema</code>, 是为了兼容其他数据库 <br>
  <code>information_schema</code>数据库不是文件夹，存在于内存中，在启动时创建</p>
</blockquote>

</div><div id="wmd-preview-section-6932" class="wmd-preview-section preview-content">

<h3 id="4-mysql物理存储结构">4. MySQL物理存储结构</h3>

<ol><li rel="1"><p>MySQL配置文件</p>

<ul>
<li><code>datadir</code> <br>
<ul>
<li>存储数据二进制文件的路径</li></ul></li></ul></li>
<li rel="2"><p>表结构的组成</p>

<ul>
<li>frm：表结构定义文件</li>
<li>MYI：索引文件</li>
<li>MYD：数据文件</li>
<li>可以用<code>hexdump -c XXX.frm</code>查看二进制文件(意义不大)</li>
<li><code>show create table tablename;</code></li>
<li><p>mysqlfrm (utilities工具包)</p>

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">shell&gt; mysqlfrm --diagnostic /data/mysql_data/aaa/.a.frm  <span class="hljs-comment">#可将frm文件转成create table的语句</span><br></code></pre></li></ul></li>
<li rel="3"><p>错误日志文件</p>

<ul>
<li><code>log_err</code> <br>
<ul>
<li>建议配置成统一的名字</li></ul></li>
<li>方便定位错误</li></ul></li>
<li rel="4"><p>慢查询日志文件</p>

<ul>
<li>将运行超过某一个时间<code>阈</code>(<em>yu四声</em>)<code>值</code>的SQL语句记录到文件 <br>
<ul>
<li>MySQL &lt; 5.1 ：以秒为单位</li>
<li>MySQL &gt;= 5.1 : 以毫秒为单位</li>
<li>MySQL &gt;= 5.5 : 可以将慢查询日志记录到表</li>
<li>MySQL &gt;= 5.6 : 以更细的粒度记录慢查询</li>
<li>MySQL &gt;= 5.7 : 增加timestamps支持</li></ul></li>
<li><code>slow_query_log_file</code> <br>
<ul>
<li>建议配置成统一的名字</li></ul></li>
<li>用于优化查询</li></ul></li>
</ol></div><div id="wmd-preview-section-footnotes" class="preview-content"></div></div></body></html>